package com.nc.delivery_project.server.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

import com.nc.delivery_project.server.domain.City;
import com.nc.delivery_project.server.domain.StateProvince;



public class CityDao extends AbstractDao<City> {
	
	protected final static String ENTITY_NAME = City.class.getSimpleName();
	
	protected final static String CITY_ID = "CITY_ID";
	protected final static String CITY_TITLE = "CITY_TITLE";
	protected final static String STATE_ID = "STATE_ID";

	public CityDao(Connection connection) throws SQLException {
		super(connection,ENTITY_NAME);
	}
	
	public CityDao() {
		super(ENTITY_NAME);
		
	}

	@Override
	protected void prepareInsertStatement(CallableStatement statement,
			City object) throws SQLException {
		statement.setInt(1, object.getStatePr().getStateId());
		statement.setString(2, object.getCityTitle());
	    statement.registerOutParameter(3, Types.NUMERIC);
	    statement.setString(4, object.getCityTitle());
	}

	@Override
	protected void prepareUpdateStatement(CallableStatement statement,
			City object) throws SQLException {
		statement.setInt(1, object.getCityId());
	     statement.setInt(2, object.getStatePr().getStateId());
	     statement.setString(3, object.getCityTitle());
	     statement.setString(4, object.getCityTitle());
		
	}

	@Override
	protected List<City> parseResultSet(ResultSet rs) throws SQLException {
		// парсинг городов
				List<City> cities = new ArrayList<>();
		
				while(rs.next()){
					    int cityId = rs.getInt(CITY_ID);
			            String cityTitle = rs.getString(CITY_TITLE);
			            int stateId = rs.getInt(STATE_ID);
			            
			            StateProvince statePr = new StateProvince(stateId);
			            
			            cities.add(new City(cityId, cityTitle, statePr));
				}
				return cities;
	}

	 @Override
	    public int create(City obj) throws SQLException {
	        try (CallableStatement statement = connection.prepareCall(getInsertQuery())) {
	            prepareInsertStatement(statement, obj);
	            statement.executeUpdate();
	         
	            return statement.getInt(3);
	        }
	    }
	 
	 public List<City> readByStateProvinceId(int stateId) throws SQLException {
		 try (PreparedStatement statement = connection.prepareStatement(getSelectByStateIdQuery())) {
	            statement.setInt(1, stateId);
	            ResultSet rs = statement.executeQuery();
	            return parseResultSet(rs);
	        }
	}
	
	protected String getSelectByStateIdQuery() {
	     return queryManager.getQuery(entityName, "select_by_state_id");        
	}
}
